Release 10.1A: OpenEdge Development:
Progress 4GL Handbook

Defining efficient queries and FOR EACH statements

This section provides some tips for defining queries and FOR EACH statements.

Using field lists

Progress allows you to specify a reduced list of fields to retrieve when you define a query or start a FOR EACH block. This is the syntax for the DEFINE QUERY field list:

DEFINE QUERY query-name FOR buffer-name    { FIELDS field ... | EXCEPT field ... } [, buffer-name ... ].

This is the syntax for the FOR EACH field list:

FOR EACH buffer-name    { FIELDS field... | EXCEPT field ... } [, buffer-name ... ].

If you specify a list of FIELDS for a buffer, only those fields are retrieved. If you specify an EXCEPT list, only those fields are not retrieved.

Under some circumstances, using a field list can reduce the amount of data transferred across the network in a client/server environment. However, there are serious limitations to the field list that mean that you should have limited use for it in most modern OpenEdge applications:

The field list option was primarily designed for use with OpenEdge DataServers, which provide a connection to non-OpenEdge databases, such as Oracle and SQL Server. These kinds of databases typically have fixed-length data values that can be much larger than their OpenEdge counterparts, where all data is stored in an efficient, variable-length form.
The field list has an effect only in a client/server environment, where your client application session has a direct connection to a database server on another system. This is not the recommended architecture for any new applications. A truly distributed application uses an AppServer to run an OpenEdge session that accesses the database and returns data to the client using temp-tables, as has been extensively discussed in this book. In this environment, you are completely in control of what fields you pass between client and server through your temp-table definitions. The field list mechanism plays no role in this.
Even if you have a database connection in a client/server environment, Progress always retrieves the entire record if you lock the record with an EXCLUSIVE-LOCK.
Progress retrieves additional fields beyond those in the field list for its own purposes, including evaluating some of the selection criteria of the query or FOR EACH.
You must remember that the field list is not the same as a display list for a browse or a field list for the fields in a frame. You define the DISPLAY list of columns in a browse independently of the query that the browse uses. If you inadvertently leave out a field in the FIELDS list of a query definition that is needed by any part of the application that uses the query, your application will generate an error at run time. This can cause serious maintenance problems if your query definitions must explicitly name every field that is used from that query anywhere on the client.

The bottom line here is that in a distributed application, you control the field list through the definition of temp-tables that pass data from server to client, and the FIELDS phrase on a query definition is not needed as part of that definition.

Structuring your selection criteria in a join

When you need to retrieve data from multiple joined tables in a single query or FOR EACH statement, it is important to put the tables into the proper sequence and to specify your selection criteria as early in the retrieval process as possible.

OpenEdge does not optimize complex joins in the same way that some other database managers do, rearranging the order of tables and fields. There is a very good reason for this. Because Progress is designed to make it easy and effective to deal with individual records and multiple levels of selection, rearranging a join in a single statement is not typically an issue. For example, this kind of nesting of data retrieval blocks is very typical in Progress business logic:

FOR EACH Customer WHERE condition>:    /* Customer processing */    FOR EACH Order OF Customer:       /* Order processing */       FOR EACH OrderLine OF Order:       /* OrderLine processing */       END.    /* More Order processing after all OrderLines have been handled. */    END.    /* Final Customer processing. */ END.

In this kind of code, the developer understands the order in which data is retrieved and is relying on that order to structure the business logic for related tables.

If you join tables in a single statement, Progress retrieves the data in the order you specify. Progress does not second-guess your selection and rearrange the retrieval for you. This means that you have to take responsibility for structuring your selection efficiently. For example, if you want to retrieve orders processed today for Customers with a CreditLimit, this kind of statement is very inefficient in Progress:

/* Less efficient selection: */ FOR EACH Customer WHERE Customer.CreditLimit NE 0,   EACH Order OF Customer WHERE OrderDate = TODAY:

Hardly any Customers have a CreditLimit of 0, so the Customer selection is going to return nearly all Customers. On the other hand, only a few Customers have placed Orders today. It would be much more efficient to identify the Orders first, and then get the Customer for each of those Orders:

/* More efficient selection: */ FOR EACH Order WHERE OrderDate = TODAY,   FIRST Customer OF Order WHERE CreditLimit EQ 0:

It’s especially important to place the selection criteria for each table as high up in the statement (that is, as close to the front) as possible. Always define the selection for each table as part of the phrase for that table’s buffer. That is, don’t write a statement such as this:

/* Inefficient selection: */ FOR EACH Customer,    EACH Order OF Customer WHERE Customer.CreditLimit NE 0 AND OrderDate = TODAY:

In this case, Progress does just what you ask it to do:

Retrieves each Customer in the Customer table in turn, into the Customer buffer, regardless of its CreditLimit or anything else.
Retrieves each Order for each Customer in turn, into the Order buffer.
Examines the CreditLimit value in the Customer buffer to see if it equals 0.
If the CreditLimit does not equal zero, examines the OrderDate in the Order buffer to see if it’s equal to today’s date.

This is clearly a very inefficient way to go through the data, especially because there is an index on the OrderDate field and another index on the CustNum field in both the Order table and the Customer table that allows Progress to identify those Orders and their Customers immediately.

If you are used to working with other data retrieval languages, you might miss the optimization of complex queries that they do, but Progress gives you control over your application behavior by presenting you with the data you ask for in the way that you ask for it. When you are writing real business logic this is much more useful than having the DBMS evaluate some complex set of expressions on a WHERE clause that joins multiple tables and return a single processed result.